package com.cafuc.jdbc;

import com.cafuc.entity.User;
import com.cafuc.util.ConfigUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class JDBCOperation {

    public void insertUser(User user) {
        Connection co = null;
        try {
            // 通过反射获取驱动
            Class.forName(ConfigUtil.properties.getProperty("jdbc.driverClassName"));
            // 通过驱动管理获取链接
            co = DriverManager.getConnection(
                    ConfigUtil.properties.getProperty("jdbc.url"),
                    ConfigUtil.properties.getProperty("jdbc.username"),
                    ConfigUtil.properties.getProperty("jdbc.password")
            );
            // 准备 sql preparedStatement
            String sql = "insert into account_user(user_name, password, create_date) " +
                    "values(?, ?, ?)";
            PreparedStatement ps = co.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, user.getUserName());
            ps.setString(2, user.getPassword());
            ps.setTimestamp(3, new Timestamp(new Date().getTime()));
            System.out.println(ps.toString());
            // 执行 sql 语句
            ps.execute();
            // 处理结果
            ResultSet rs = ps.getGeneratedKeys();
            while(rs.next()) {
                user.setId(rs.getInt(1));
            }
            System.out.println(user.getId());
            // 提交事务
//            co.setAutoCommit(false);
//            co.commit();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                // 关闭连接
                if (co != null) {
                    co.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public List<User> queryUsers() {
        Connection co = null;
        List<User> users = new ArrayList<>();
        try {
            // 通过反射获取驱动
            Class.forName(ConfigUtil.properties.getProperty("jdbc.driverClassName"));
            // 通过驱动管理获取链接
            co = DriverManager.getConnection(
                    ConfigUtil.properties.getProperty("jdbc.url"),
                    ConfigUtil.properties.getProperty("jdbc.username"),
                    ConfigUtil.properties.getProperty("jdbc.password")
            );
            // 准备 sql preparedStatement
            String sql = "select * from account_user";
            PreparedStatement ps = co.prepareStatement(sql);
            System.out.println(ps.toString());
            // 执行 sql 语句
            ResultSet rs = ps.executeQuery();
            // 处理结果
            while(rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUserName(rs.getString("user_name"));
                user.setPassword(rs.getString("password"));
                user.setCreateDate(rs.getTimestamp("create_date"));
                users.add(user);
            }
            // 提交事务
//            co.setAutoCommit(false);
//            co.commit();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                // 关闭连接
                if (co != null) {
                    co.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return users;
    }

    public static void main(String[] args) {
        JDBCOperation jdbc = new JDBCOperation();
        User user = new User();
        user.setUserName("sun");
        user.setPassword("1111");
//        jdbc.insertUser(user);
        List<User> users = jdbc.queryUsers();
        System.out.println(users);
    }
}